"""
数据导出工具模块
支持Excel、PDF、CSV等格式的数据导出
"""

import io
import json
import csv
from datetime import datetime
from typing import List, Dict, Any, Optional
from sqlalchemy.orm import Session

try:
    import openpyxl
    from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
    from openpyxl.utils import get_column_letter
    EXCEL_AVAILABLE = True
except ImportError:
    EXCEL_AVAILABLE = False

try:
    from reportlab.lib.pagesizes import letter, A4
    from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
    from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
    from reportlab.lib import colors
    from reportlab.lib.units import inch
    from reportlab.pdfbase import pdfmetrics
    from reportlab.pdfbase.ttfonts import TTFont
    PDF_AVAILABLE = True
except ImportError:
    PDF_AVAILABLE = False

try:
    import pandas as pd
    PANDAS_AVAILABLE = True
except ImportError:
    PANDAS_AVAILABLE = False

from database import Submission, SubmissionDetail, Question
import crud


class ExportManager:
    """数据导出管理器"""
    
    def __init__(self, db: Session):
        self.db = db
    
    def export_student_records(self, 
                             student_name: Optional[str] = None,
                             class_id: Optional[int] = None,
                             start_date: Optional[str] = None,
                             end_date: Optional[str] = None,
                             format_type: str = "excel") -> Dict[str, Any]:
        """导出学生记录"""
        
        # 获取数据
        query = self.db.query(Submission)
        
        if student_name:
            query = query.filter(Submission.student_name.like(f"%{student_name}%"))
        if class_id:
            query = query.filter(Submission.class_id == class_id)
        if start_date:
            query = query.filter(Submission.submission_date >= start_date)
        if end_date:
            query = query.filter(Submission.submission_date <= end_date)
        
        submissions = query.order_by(Submission.submission_time.desc()).all()
        
        if format_type.lower() == "excel":
            return self._export_to_excel(submissions, "学生答题记录")
        elif format_type.lower() == "csv":
            return self._export_to_csv(submissions)
        elif format_type.lower() == "pdf":
            return self._export_to_pdf(submissions, "学生答题记录")
        else:
            raise ValueError(f"不支持的导出格式: {format_type}")
    
    def export_teacher_dashboard_data(self, class_id: int, format_type: str = "excel") -> Dict[str, Any]:
        """导出教师看板数据"""
        
        # 获取班级数据
        dashboard_data = crud.get_teacher_dashboard_data(self.db, class_id)
        
        if format_type.lower() == "excel":
            return self._export_dashboard_to_excel(dashboard_data, class_id)
        elif format_type.lower() == "csv":
            return self._export_dashboard_to_csv(dashboard_data, class_id)
        elif format_type.lower() == "pdf":
            return self._export_dashboard_to_pdf(dashboard_data, class_id)
        else:
            raise ValueError(f"不支持的导出格式: {format_type}")
    
    def _export_to_excel(self, submissions: List[Submission], title: str) -> Dict[str, Any]:
        """导出到Excel格式"""
        if not EXCEL_AVAILABLE:
            return self._export_to_csv(submissions)
        
        try:
            # 创建工作簿
            wb = openpyxl.Workbook()
            ws = wb.active
            ws.title = title
            
            # 设置标题
            ws['A1'] = title
            ws['A1'].font = Font(size=16, bold=True)
            ws['A1'].alignment = Alignment(horizontal='center')
            ws.merge_cells('A1:H1')
            
            # 设置表头
            headers = ['学生姓名', '班级', '得分', '正确率', '正确题数', '总题数', '提交日期', '提交时间']
            for col, header in enumerate(headers, 1):
                cell = ws.cell(row=3, column=col, value=header)
                cell.font = Font(bold=True)
                cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
                cell.alignment = Alignment(horizontal='center')
            
            # 填充数据
            for row, submission in enumerate(submissions, 4):
                accuracy_rate = round((submission.correct_answers / submission.total_questions * 100), 1) if submission.total_questions > 0 else 0
                
                ws.cell(row=row, column=1, value=submission.student_name)
                ws.cell(row=row, column=2, value=f"{submission.class_id}班")
                ws.cell(row=row, column=3, value=submission.score)
                ws.cell(row=row, column=4, value=f"{accuracy_rate}%")
                ws.cell(row=row, column=5, value=submission.correct_answers)
                ws.cell(row=row, column=6, value=submission.total_questions)
                ws.cell(row=row, column=7, value=submission.submission_date)
                ws.cell(row=row, column=8, value=submission.submission_time.strftime("%Y-%m-%d %H:%M:%S"))
            
            # 调整列宽
            for col in range(1, 9):
                ws.column_dimensions[get_column_letter(col)].width = 15
            
            # 保存到内存
            output = io.BytesIO()
            wb.save(output)
            output.seek(0)
            
            return {
                "status": "success",
                "format": "excel",
                "filename": f"student_records_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx",
                "content": output.getvalue(),
                "content_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                "total_records": len(submissions)
            }
            
        except Exception as e:
            # 如果Excel导出失败，回退到CSV
            return self._export_to_csv(submissions)
    
    def _export_to_csv(self, submissions: List[Submission]) -> Dict[str, Any]:
        """导出到CSV格式"""
        output = io.StringIO()
        writer = csv.writer(output)
        
        # 写入表头
        writer.writerow(['学生姓名', '班级', '得分', '正确率', '正确题数', '总题数', '提交日期', '提交时间'])
        
        # 写入数据
        for submission in submissions:
            accuracy_rate = round((submission.correct_answers / submission.total_questions * 100), 1) if submission.total_questions > 0 else 0
            writer.writerow([
                submission.student_name,
                f"{submission.class_id}班",
                submission.score,
                f"{accuracy_rate}%",
                submission.correct_answers,
                submission.total_questions,
                submission.submission_date,
                submission.submission_time.strftime("%Y-%m-%d %H:%M:%S")
            ])
        
        content = output.getvalue().encode('utf-8-sig')  # 使用UTF-8 BOM以支持中文
        
        return {
            "status": "success",
            "format": "csv",
            "filename": f"student_records_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv",
            "content": content,
            "content_type": "text/csv; charset=utf-8",
            "total_records": len(submissions)
        }
    
    def _export_to_pdf(self, submissions: List[Submission], title: str) -> Dict[str, Any]:
        """导出到PDF格式"""
        if not PDF_AVAILABLE:
            return self._export_to_csv(submissions)
        
        try:
            output = io.BytesIO()
            doc = SimpleDocTemplate(output, pagesize=A4)
            story = []
            
            # 样式
            styles = getSampleStyleSheet()
            title_style = ParagraphStyle(
                'CustomTitle',
                parent=styles['Heading1'],
                fontSize=18,
                spaceAfter=30,
                alignment=1  # 居中
            )
            
            # 标题
            story.append(Paragraph(title, title_style))
            story.append(Spacer(1, 12))
            
            # 表格数据
            data = [['学生姓名', '班级', '得分', '正确率', '正确题数', '总题数', '提交时间']]
            
            for submission in submissions:
                accuracy_rate = round((submission.correct_answers / submission.total_questions * 100), 1) if submission.total_questions > 0 else 0
                data.append([
                    submission.student_name,
                    f"{submission.class_id}班",
                    str(submission.score),
                    f"{accuracy_rate}%",
                    str(submission.correct_answers),
                    str(submission.total_questions),
                    submission.submission_time.strftime("%Y-%m-%d %H:%M")
                ])
            
            # 创建表格
            table = Table(data)
            table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.grey),
                ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 12),
                ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
                ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
                ('GRID', (0, 0), (-1, -1), 1, colors.black)
            ]))
            
            story.append(table)
            doc.build(story)
            
            return {
                "status": "success",
                "format": "pdf",
                "filename": f"student_records_{datetime.now().strftime('%Y%m%d_%H%M%S')}.pdf",
                "content": output.getvalue(),
                "content_type": "application/pdf",
                "total_records": len(submissions)
            }
            
        except Exception as e:
            # 如果PDF导出失败，回退到CSV
            return self._export_to_csv(submissions)
    
    def _export_dashboard_to_excel(self, dashboard_data: Dict[str, Any], class_id: int) -> Dict[str, Any]:
        """导出教师看板数据到Excel"""
        if not EXCEL_AVAILABLE:
            return self._export_dashboard_to_csv(dashboard_data, class_id)
        
        try:
            wb = openpyxl.Workbook()
            
            # 概览数据工作表
            ws1 = wb.active
            ws1.title = "班级概览"
            
            # 标题
            ws1['A1'] = f"{class_id}班 教学数据分析报告"
            ws1['A1'].font = Font(size=16, bold=True)
            ws1.merge_cells('A1:D1')
            
            # 基本统计
            ws1['A3'] = "基本统计"
            ws1['A3'].font = Font(size=14, bold=True)
            
            stats = [
                ["总学生数", dashboard_data.get('total_students', 0)],
                ["平均分", dashboard_data.get('average_score', 0)],
                ["最高分", dashboard_data.get('max_score', 0)],
                ["最低分", dashboard_data.get('min_score', 0)],
                ["及格率", f"{dashboard_data.get('pass_rate', 0)}%"],
                ["优秀率", f"{dashboard_data.get('excellent_rate', 0)}%"]
            ]
            
            for i, (key, value) in enumerate(stats, 4):
                ws1[f'A{i}'] = key
                ws1[f'B{i}'] = value
            
            # 学生表现工作表
            if 'student_performance' in dashboard_data:
                ws2 = wb.create_sheet("学生表现")
                headers = ['学生姓名', '得分', '正确率', '薄弱知识点']
                
                for col, header in enumerate(headers, 1):
                    cell = ws2.cell(row=1, column=col, value=header)
                    cell.font = Font(bold=True)
                
                for row, student in enumerate(dashboard_data['student_performance'], 2):
                    ws2.cell(row=row, column=1, value=student.get('student_name', ''))
                    ws2.cell(row=row, column=2, value=student.get('score', 0))
                    ws2.cell(row=row, column=3, value=f"{student.get('accuracy_rate', 0)}%")
                    ws2.cell(row=row, column=4, value=', '.join(student.get('weak_knowledge_points', [])))
            
            # 保存到内存
            output = io.BytesIO()
            wb.save(output)
            output.seek(0)
            
            return {
                "status": "success",
                "format": "excel",
                "filename": f"class_{class_id}_analysis_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx",
                "content": output.getvalue(),
                "content_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            }
            
        except Exception as e:
            return self._export_dashboard_to_csv(dashboard_data, class_id)
    
    def _export_dashboard_to_csv(self, dashboard_data: Dict[str, Any], class_id: int) -> Dict[str, Any]:
        """导出教师看板数据到CSV"""
        output = io.StringIO()
        writer = csv.writer(output)
        
        # 基本信息
        writer.writerow([f"班级{class_id}教学数据分析报告"])
        writer.writerow([])
        writer.writerow(["基本统计"])
        writer.writerow(["总学生数", dashboard_data.get('total_students', 0)])
        writer.writerow(["平均分", dashboard_data.get('average_score', 0)])
        writer.writerow(["及格率", f"{dashboard_data.get('pass_rate', 0)}%"])
        writer.writerow(["优秀率", f"{dashboard_data.get('excellent_rate', 0)}%"])
        writer.writerow([])
        
        # 学生表现
        if 'student_performance' in dashboard_data:
            writer.writerow(["学生表现"])
            writer.writerow(['学生姓名', '得分', '正确率', '薄弱知识点'])
            
            for student in dashboard_data['student_performance']:
                writer.writerow([
                    student.get('student_name', ''),
                    student.get('score', 0),
                    f"{student.get('accuracy_rate', 0)}%",
                    ', '.join(student.get('weak_knowledge_points', []))
                ])
        
        content = output.getvalue().encode('utf-8-sig')
        
        return {
            "status": "success",
            "format": "csv",
            "filename": f"class_{class_id}_analysis_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv",
            "content": content,
            "content_type": "text/csv; charset=utf-8"
        }
    
    def _export_dashboard_to_pdf(self, dashboard_data: Dict[str, Any], class_id: int) -> Dict[str, Any]:
        """导出教师看板数据到PDF"""
        if not PDF_AVAILABLE:
            return self._export_dashboard_to_csv(dashboard_data, class_id)
        
        # 简化实现，回退到CSV
        return self._export_dashboard_to_csv(dashboard_data, class_id)
